package com.F02.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;

import com.F02.actionform.F020203ActionForm;
import com.F02.dao.F020203DAO;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;

/**
 * @leip
 * @非标准产品维护
 * @数据库操作类
 * @方法命名规则：静态参数名称、例如：查询方法，方法名 QUERYLIST();
 * @数据库某字段如果的新增值，如果要取当前时间，请在数据库中表时间的字段上设置GETDATE(), @ insert语句执行时，会自动插入时间。避免系统时间于服务器时间不统一造成错误。 @ 例表c_customers的customer_date字段。

 */

public class F020203DAO extends DbOperationExt{
	
	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F020203DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F020203DAO() {
		super();
	}

	public F020203DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @查询
	 */
	public List QUERYLIST(F020203ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		/**判断查询条件，拼装查询SQL*/		
		/**产品类别*/
		if(actionForm.getProductType()!=null&&!actionForm.getProductType().equals("")){
			condition.append(" and a.product_type = '"+actionForm.getProductType()+"' ");
		}
		/**模型类别*/
		if(actionForm.getModelCode()!=null&&!actionForm.getModelCode().equals("")){
			condition.append(" and a.model_type = '"+actionForm.getModelCode()+"' ");
		}
		/**工作号*/
		if(actionForm.getManufacturingOrder()!=null&&!actionForm.getManufacturingOrder().equals("")){
			condition.append(" and a.manufacturing_order >= '"+CommonTools.null2String(actionForm.getManufacturingOrder())+"' ");
		}
		if(actionForm.getManufacturingOrderBy()!=null&&!actionForm.getManufacturingOrderBy().equals("")){
			condition.append(" and a.manufacturing_order <= '"+CommonTools.null2String(actionForm.getManufacturingOrderBy())+"' ");
		}
		/**制造代码*/
		if(actionForm.getProductCode()!=null&&!actionForm.getProductCode().equals("")){
			condition.append(" and a.product_code >= '"+CommonTools.null2String(actionForm.getProductCode())+"' ");
		}
		if(actionForm.getProductCodeBy()!=null&&!actionForm.getProductCodeBy().equals("")){
			condition.append(" and a.product_code <= '"+CommonTools.null2String(actionForm.getProductCodeBy())+"' ");
		}
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		
		msql.append(" SELECT a.code,a.manufacturing_order,a.product_code,a.family_code, ");       
		msql.append(" b.[name] product_name,c.[name] model_name,");
		msql.append(" a.[description],a.material_cost, ");	
		msql.append(" a.manufacturing_hours,a.product_type ");		
		msql.append(" FROM t_not_standard_products a, t_product_type b, ");
		msql.append(" t_product_model c ");
		msql.append(" where a.product_type = b.code and a.model_type = c.code ");		
		msql.append(" "+condition.toString());
		msql.append(" order by a.manufacturing_order ");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());						
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("code", CommonTools.null2String(rest
						.getString("code")));
				map.put("manufacturingOrder", CommonTools.null2String(rest
						.getString("manufacturing_order")));
				map.put("productCode", CommonTools.null2String(rest
						.getString("product_code")));
				map.put("familyCode", CommonTools.null2String(rest
						.getString("family_code")));
				map.put("productName", CommonTools.null2String(rest
						.getString("product_name")));
				map.put("modelCode", CommonTools.null2String(rest
						.getString("model_name")));
				map.put("description", CommonTools.null2String(rest
						.getString("description")));				
				map.put("materialCost",CommonTools.convertCurrencyRMB(rest
						.getString("material_cost")));				
				map.put("manufacturingHours", CommonTools.convertNumber(rest
						.getString("manufacturing_hours")));				
				map.put("productType", CommonTools.convertNumber(rest
						.getString("product_type")));
				list.add(map);
			}			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	
	/**
	 * @新增保存
	 * @要记录操作，在插入后要向操作记录表也插一条语句，也就是用执行多天SQL， @ 所有用super.executeUpdate(sqlList,true)执行
	 */
	public boolean SAVE_INSERT(F020203ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) {
		boolean falg = false;
		List sqlList = new ArrayList();
		/** 存放SQL的LIST */
		StringBuffer yhczsql = new StringBuffer();
		/** 操作记录SQL */
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		/** 获得操作记录SQL */
		StringBuffer msql = new StringBuffer();
		msql.append("INSERT INTO t_not_standard_products( ");		
		msql.append("manufacturing_order, ");
		msql.append("product_code,product_type, ");
		msql.append("model_type,family_code,material_cost, ");
		msql.append("manufacturing_hours,description ");		
		msql.append(" )VALUES ( ");				
		msql.append("'" + CommonTools.null2String(actionForm.getManufacturingOrder()) + "', ");
		msql.append("'" + CommonTools.null2String(actionForm.getProductCode()) + "', ");
		msql.append("'" + actionForm.getProductType() + "', ");		
		msql.append("'" + actionForm.getModelCode() + "', ");
		msql.append("'" + CommonTools.null2String(actionForm.getFamilyCode()) + "','0.00', ");
		msql.append(" " + CommonTools.null2String(actionForm.getManufacturingHours()) + ", ");
		msql.append("'" + CommonTools.null2String(actionForm.getDescription()) + "' )");

		try {
			log.info("SAVE_INSERT sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			falg = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");/**成功*/
		} catch (SQLException ex) {
			if ("1".equals(String.valueOf(ex.getErrorCode()))) {
				/** ex.getErrorCode()返回一，表示主键冲突 */
				actionForm.setErrorFlag("1");/** 主键冲突! */
			} else {
				actionForm.setErrorFlag("1");/**保存失败*/
			}
		} catch (Exception e) {
			actionForm.setErrorFlag("4");/**  */
			e.printStackTrace();
			super.rollback();

		}
		super.commit();

		return falg;
	}
	/**
	 * @更新价格
	 */
	public boolean UPDATE_COST(F020203ActionForm actionForm) throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		
		StringBuffer msql = new StringBuffer();
		msql.append(" UPDATE t_not_standard_products ");
		msql.append(" SET material_cost= adyh.FUN_GETCOSTNSTAND("+actionForm.getCode()+")");
		msql.append(" WHERE code = "+  actionForm.getCode() + " ");		
		try {
			log.info("UPDATE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);			

		} catch (Exception e) {			
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}
	/**
	 * @编辑
	 */
	public void EDIT(F020203ActionForm actionForm, HttpServletRequest request) {
		ResultSet rest = null;
		StringBuffer msql = new StringBuffer();		
		msql.append(" SELECT a.code,a.manufacturing_order,a.product_code,");
		msql.append(" a.manufacturing_hours,c.customers_name, ");	
		msql.append(" b.[model],b.customer_delivery_date, ");
		msql.append(" b.[description] order_description, ");
		msql.append(" a.product_type,a.model_type,");
		msql.append(" a.family_code,a.[description] ");
		msql.append(" FROM t_not_standard_products a,c_orders b,c_customers c ");		
		msql.append(" WHERE a.manufacturing_order=b.manufacturing_order ");
		msql.append(" and b.customer_code=c.customers_code and a.code='"
				+ CommonTools.null2String(request
						.getParameter("code").trim()) + "'");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				actionForm.setCode(CommonTools.null2String(rest
						.getString("code")));
				actionForm.setManufacturingOrder(CommonTools.null2String(rest
						.getString("manufacturing_order")));
				actionForm.setProductCode(CommonTools.null2String(rest
						.getString("product_code")));				
				actionForm.setManufacturingHours(CommonTools.null2String(rest
						.getString("manufacturing_hours")));
				actionForm.setCustomerName(CommonTools.null2String(rest.
						getString("customers_name")));
				actionForm.setModel(CommonTools.null2String(rest.
						getString("model")));
				actionForm.setDeliverDate(CommonTools.null2String(CommonTools.getDateTime((rest
						.getDate("customer_delivery_date")))));
				actionForm.setOrderDescription(CommonTools.null2String(rest.
						getString("order_description")));
				actionForm.setProductType(CommonTools.null2String(rest.
						getString("product_type")));
				actionForm.setModelCode(CommonTools.null2String(rest.
						getString("model_type")));
				actionForm.setFamilyCode(CommonTools.null2String(rest.
						getString("family_code")));
				actionForm.setFamilyCodeBy(CommonTools.null2String(rest.
						getString("family_code")));
				actionForm.setDescription(CommonTools.null2String(rest.
						getString("description")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}
	}

	/**
	 * @编辑保存
	 */
	public boolean SAVE_UPDATE(F020203ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		StringBuffer msql = new StringBuffer();
		msql.append(" UPDATE t_not_standard_products  SET ");
		msql.append(" manufacturing_order = '" + CommonTools.null2String(actionForm.getManufacturingOrder())+ "', ");		
		msql.append(" product_code = '"+ CommonTools.null2String(actionForm.getProductCode()) + "', ");
		msql.append(" manufacturing_hours = "+ CommonTools.null2String(actionForm.getManufacturingHours()) + ", ");
		msql.append(" product_type = '"+ actionForm.getProductType() + "', ");
		msql.append(" model_type = '"+ actionForm.getModelCode() + "', ");
		msql.append(" family_code = '"+ CommonTools.null2String(actionForm.getFamilyCode()) + "', ");
		msql.append(" description = '" + CommonTools.null2String(actionForm.getDescription()) + "' ");
		msql.append(" WHERE  code = '"+  actionForm.getCode() + "' ");
		try {
			log.info("SAVE_UPDATE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");

		} catch (Exception e) {
			actionForm.setErrorFlag("1");
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}
	/**
	 * @编辑
	 */
	public void BOM(F020203ActionForm actionForm,HttpServletRequest request) {
		ResultSet rest = null;		
		StringBuffer msql = new StringBuffer();			
		msql.append(" SELECT a.code,manufacturing_order,product_code,");		
		msql.append(" a.product_type,a.model_type,");
		msql.append(" b.name product_name,c.name model_name,");
		msql.append(" family_code,a.description ");
		msql.append(" FROM t_not_standard_products a,t_product_type b,t_product_model c");		
		msql.append(" WHERE a.product_type=b.code and a.model_type=c.code and a.code='"
				+ CommonTools.null2String(request
						.getParameter("code").trim()) + "'");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;				
				actionForm.setManufacturingOrder(CommonTools.null2String(rest
						.getString("manufacturing_order")));
				actionForm.setProductCode(CommonTools.null2String(rest
						.getString("product_code")));				
				actionForm.setProductType(CommonTools.null2String(rest.
						getString("product_type")));
				actionForm.setProductName(CommonTools.null2String(rest.
						getString("product_name")));
				actionForm.setModelCode(CommonTools.null2String(rest.
						getString("model_type")));
				actionForm.setModelName(CommonTools.null2String(rest.
						getString("model_name")));
				actionForm.setFamilyCode(CommonTools.null2String(rest.
						getString("family_code")));
				actionForm.setOrderDescription(CommonTools.null2String(rest.
						getString("description")));
			}			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}
	}
	/**
	 * @复制
	 */
	public boolean COPYBOM(F020203ActionForm actionForm,HttpServletRequest request) {		
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer msql = new StringBuffer();
		StringBuffer msql2 = new StringBuffer();
		StringBuffer condition = new StringBuffer();		
		//拼接where条件
		if(actionForm.getManufacturingOrderBy()!=null&&!actionForm.getManufacturingOrderBy().equals("")){
			condition.append(" and a.manufacturing_order = '"+actionForm.getManufacturingOrderBy()+"' ");
		}
		if(actionForm.getProductCodeBy()!=null&&!actionForm.getProductCodeBy().equals("")){
			condition.append(" and a.product_code = '"+actionForm.getProductCodeBy()+"' ");
		}
		if(actionForm.getFamilyCodeBy()!=null&&!actionForm.getFamilyCodeBy().equals("")){
			condition.append(" and a.family_code = '"+actionForm.getFamilyCodeBy()+"' ");
		}
		
		//先删除已有BOMDetailList
		msql.append(" DELETE FROM t_not_standard_detail WHERE standard_code='"
				+ CommonTools.null2String(request
						.getParameter("code").trim()) + "'");		
		//拷贝其他产品BOM
		msql2.append(" INSERT INTO t_not_standard_detail");		
		msql2.append(" SELECT '"+CommonTools.null2String(request.getParameter("code").trim())+"',");
		msql2.append(" b.serial,b.material_code,");
		msql2.append(" b.quantity,b.unit_price ");
		msql2.append(" FROM "+actionForm.getFromTable()+"_products a, ");
		msql2.append(" "+actionForm.getFromTable()+"_detail b ");		
		msql2.append(" WHERE a.code=b.standard_code ");
		msql2.append(" "+condition.toString());
		try {
			sqlList.add(msql);
			sqlList.add(msql2);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
						
		} catch (Exception e) {
			actionForm.setErrorFlag("1");
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}	
	/**
	 * @删除
	 */
	public boolean DELETE(F020203ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo, HttpServletRequest request)
			throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);		
		StringBuffer msql = new StringBuffer();
		msql.append(" DELETE FROM t_not_standard_products ");
		msql.append(" WHERE ");
		msql.append(" code = "
				+ actionForm.getId().trim());		
		try {
			log.info("delete sql is " + msql.toString());			
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
		} catch (Exception e) {
			e.printStackTrace();
			super.rollback();
			actionForm.setErrorFlag("1");
		}
		super.commit();
		return flag;
	}	
	
}
